package com.study.dao.impl;

import com.study.bean.entity.Dormitory;
import com.study.bean.entity.Student;
import com.study.bean.query.DormitoryQuery;
import com.study.bean.vo.DormitoryVO;
import com.study.dao.DormitoryDao;
import com.study.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class DormitoryDaoImpl implements DormitoryDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public List<DormitoryVO> selectByPage(DormitoryQuery dormitoryQuery) {
        String sql = "SELECT \n" +
                "    d.id,\n" +
                "    d.dormitory_id,\n" +
                "    d.building_id,\n" +
                "    d.occupancy,\n" +
                "    (d.occupancy - IFNULL(s.student_count, 0)) AS remaining_capacity,\n" +
                "    da.m_name\n" +
                "FROM \n" +
                "    dormitories d\n" +
                "LEFT JOIN (\n" +
                "    SELECT \n" +
                "        building_id, \n" +
                "        dormitory_id, \n" +
                "        COUNT(*) AS student_count\n" +
                "    FROM \n" +
                "        students\n" +
                "    GROUP BY \n" +
                "        building_id, \n" +
                "        dormitory_id\n" +
                ") s ON d.dormitory_id = s.dormitory_id AND d.building_id = s.building_id\n" +
                "LEFT JOIN dormitory_buildings db ON d.building_id = db.building_id\n" +
                "LEFT JOIN dormitory_admins da ON db.administrator_id = da.admin_id where 1=1 ";
        List<Object> list = new ArrayList<>();
        System.out.println(dormitoryQuery);

        if (dormitoryQuery.getDormitoryId() != null) {
            sql += " AND d.dormitory_id = ? ";
            list.add(dormitoryQuery.getDormitoryId());
        }
        if (dormitoryQuery.getBuildingId() != null) {
            sql += " AND d.building_id = ? ";
            list.add(dormitoryQuery.getBuildingId());
        }
        if (dormitoryQuery.getAdminId() != null) {
            sql += " AND da.admin_id = ? ";
            list.add(dormitoryQuery.getAdminId());
        }
        // 添加 LIMIT 语句并添加参数
        sql += " LIMIT ?, ?";
        int offset = (dormitoryQuery.getPage() - 1) * dormitoryQuery.getLimit();
        list.add(offset);
        list.add(dormitoryQuery.getLimit());
        List<DormitoryVO> list1 = template.query(sql,
                new BeanPropertyRowMapper<>(DormitoryVO.class), list.toArray());
        list1.forEach(System.out::println);
        return list1;
    }

    @Override
    public int selectTotalCount(DormitoryQuery dormitoryQuery) {
        String sql = "SELECT COUNT(*) " +
                "FROM (" +
                "   SELECT d.*, db.administrator_id " +
                "   FROM dormitories d, dormitory_buildings db " +
                "   WHERE d.building_id = db.building_id" +
                ") dp, dormitory_admins da " +
                "WHERE dp.administrator_id = da.admin_id ";
        List<Object> list = new ArrayList<>();
        System.out.println(dormitoryQuery);
        if (dormitoryQuery.getDormitoryId() != null) {
            sql += " AND dp.dormitory_id = ?";
            list.add(dormitoryQuery.getDormitoryId());
        }
        if (dormitoryQuery.getBuildingId() != null) {
            sql += " AND dp.building_id = ?";
            list.add(dormitoryQuery.getBuildingId());
        }
        if (dormitoryQuery.getAdminId() != null) {
            sql += " AND dp.administrator_id  = ? ";
            list.add(dormitoryQuery.getAdminId());
        }
        Integer i = template.queryForObject(sql, Integer.class, list.toArray());
        return i;
    }

    @Override
    public Integer addDormitory(Dormitory dormitory) {

        String sql = "INSERT INTO dormitories(dormitory_id,building_id) VALUES(?,?)\n";
        int update = template.update(sql, dormitory.getDormitoryId(), dormitory.getBuildingId());
        return update;
    }

    @Override
    public List<Student> viewStu(DormitoryQuery dormitoryQuery) {

        String  sql = "select * from students where dormitory_id = ? and building_id = ?";
        List<Student> query = template.query(sql, new BeanPropertyRowMapper<>(Student.class), dormitoryQuery.getDormitoryId(), dormitoryQuery.getBuildingId());

        return query;
    }

}
